package My

import (
	"encoding/json"
	"errors"
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"reflect"
	"sort"
	"strconv"
	"strings"
)

// indexNameColumnMerge 每个表的索引列数据信息合并及抽取
func (my TableInfoMeta) indexNameColumnMerge(s global.TablesMetaInfoInput) global.IndexMerge {
	var indexName = make(map[string][]global.IndexColumn)
	var indexType = make(map[string]string)
	var indexColumnSeq = make(map[string][]string)
	var indexNameOrderBy []string
	for _, v := range s.IndexColumn {
		if _, ok := indexType[v.IndexName]; !ok {
			var tmpIndexType string
			switch v.ColumnKey {
			case "P":
				tmpIndexType = "pri"
				v.IndexName = "PRIMARY"
			case "U":
				tmpIndexType = "uni"
			case "M":
				tmpIndexType = "mul"
			}
			indexType[v.IndexName] = tmpIndexType
			indexNameOrderBy = append(indexNameOrderBy, v.IndexName)
		}
		if v1, ok := indexName[v.IndexName]; ok {
			indexName[v.IndexName] = append(v1, v)
		} else {
			indexName[v.IndexName] = []global.IndexColumn{v}
		}
		if v1, ok := indexColumnSeq[v.IndexName]; ok {
			indexColumnSeq[v.IndexName] = append(v1, v.ColumnSeq)
		} else {
			indexColumnSeq[v.IndexName] = []string{v.ColumnSeq}
		}
	}
	return global.IndexMerge{
		Schema:           s.TableInfo.Schema,
		Table:            s.TableInfo.Table,
		IndexColumn:      indexName,
		IndexType:        indexType,
		OrderByIndexName: indexNameOrderBy,
		IndexColumnSeq:   indexColumnSeq,
	}
}
func (my TableInfoMeta) IndexMerge(s global.TablesMetaInfoInput) global.IndexMerge {
	if len(s.IndexColumn) == 0 {
		return global.IndexMerge{}
	}
	//索引列处理，联合索引进行列合并
	//去除主键索引列、唯一索引列、普通索引列的所有列明
	//索引列处理，联合索引进行列合并
	//去除主键索引列、唯一索引列、普通索引列的所有列明
	p := my.indexNameColumnMerge(s)
	return p
}

// 一个索引的包含的列类型对比
func (my TableInfoMeta) typeSelection(tsl []global.IndexColumn) []int {
	var typeDefinition = map[string]int{"int": 1, "time": 2, "string": 3, "text": 4}
	var intType = []string{"FLOAT", "DOUBLE", "DECIMAL", "NUMERIC"}
	var timeType = []string{"DATE", "TIME", "DATETIME", "TIMESTAMP", "YEAR"}
	var stringType = []string{"CHAR", "VARCHAR"}
	var typeSeq []int
	for _, v := range tsl {
		var exist = false
		if strings.HasSuffix(strings.ToUpper(v.ColumnType), "INT") {
			typeSeq = append(typeSeq, typeDefinition["int"])
			exist = true
		}
		for _, v1 := range intType {
			if strings.HasPrefix(strings.ToUpper(v.ColumnType), v1) {
				typeSeq = append(typeSeq, typeDefinition["int"])
				exist = true
			}
		}
		for _, v1 := range timeType {
			if strings.HasPrefix(strings.ToUpper(v.ColumnType), v1) {
				typeSeq = append(typeSeq, typeDefinition["time"])
				exist = true
			}
		}
		for _, v1 := range stringType {
			if strings.HasPrefix(strings.ToUpper(v.ColumnType), v1) {
				typeSeq = append(typeSeq, typeDefinition["string"])
				exist = true
			}
		}
		if !exist {
			typeSeq = append(typeSeq, 100)
		}
	}
	return typeSeq
}

// 多个索引的数据类型比较，按照规则选择一个或多个相同的
func (my TableInfoMeta) typeSum(ll map[string][]int) (iName string) {
	iValue := 10000
	var iSlice []int
	for k, v := range ll {
		var iSum int
		for _, v1 := range v {
			iSum += v1
		}
		if iSum < iValue {
			iName = k
			iSlice = v
			iValue = iSum
		} else if iSum == iValue {
			for k2, v2 := range iSlice {
				if v2 < v[k2] {
					iName = k
					iSlice = v
					iValue = iSum
				}
			}
		}
	}
	return
}

// mulCardinality 普通索引通过离散度来进行筛选
func (my TableInfoMeta) mulCardinality(mulSelection map[string] /*indexType*/ []string /*indexName*/, i global.IndexMerge) any {
	//先检查索引前导列的离散度（第一列离散度值，默认选择一个离散度最高的，如果离散度比较低，建议先analyze table一下）
	var cardinality uint64 = 0
	event := "[mulCardinality]"
	var indexName string
	if len(mulSelection) == 0 {
		return nil
	}
	for _, v := range mulSelection {
		for _, v1 := range v {
			var curryIndexCardinality uint64 = 0
			for _, v2 := range i.IndexColumn[v1] {
				if len(v2.Cardinality) > 0 {
					if parseUint, err2 := strconv.ParseUint(v2.Cardinality, 10, 64); err2 != nil {
						WLog.Warn(fmt.Sprintf("(%d) %v string strconv uint64 fail. string is {%v}, error info is %v", 1, event, v2.Cardinality, err2))
					} else {
						if parseUint > curryIndexCardinality {
							curryIndexCardinality = parseUint
						}
					}
				}
			}
			if curryIndexCardinality >= cardinality {
				cardinality = curryIndexCardinality
				indexName = v1
			}
		}
	}
	return map[string]any{"indexName": indexName, "cardinality": cardinality}
}
func (my TableInfoMeta) keyChoice(i global.IndexMerge) global.IndexSelection {
	//候选索引,先选择主键索引且索引列小于3，然后是唯一索引、普通索引
	var (
		candidateIndex                                             = map[string][]string{"pri": {}, "uni": {}, "mui": {}}
		cardinalityGather                                          []any
		maxCardinalityValue                                        uint64
		cardinalityMaxGather                                       = make(map[string]uint64)
		optimalIndexColumnType                                     []string
		multipleMaxCardinalityGather                               map[string] /*索引名*/ []int //类型定义值
		priSingleSelection, uniSingleSelection, muiSingleSelection = make(map[string][]string), make(map[string][]string), make(map[string][]string)
		priMullSelection, uniMullSelection, muiMullSelection       = make(map[string][]string), make(map[string][]string), make(map[string][]string)
	)
	for k, v := range i.IndexType {
		for _, v1 := range []string{"pri", "uni", "mul"} {
			if strings.EqualFold(v1, v) {
				if len(i.IndexColumn[k]) > 0 {
					candidateIndex[v1] = append(candidateIndex[v1], k)
				}
			}
		}
	}
	//没有索引则退出
	exit := true
	for _, v := range candidateIndex {
		if len(v) > 0 {
			exit = false
		}
	}
	if exit {
		return global.IndexSelection{}
	}
	//筛选主键或者唯一索引为单列的
	for k, v := range candidateIndex {
		var fp, fu, fm []string
		var mp, mu, mm []string
		for _, v1 := range v {
			if v2, ok := i.IndexColumn[v1]; ok {
				if len(v2) == 1 {
					if strings.EqualFold(k, "pri") {
						fp = append(fp, v1)
					} else if strings.EqualFold(k, "uni") {
						fu = append(fu, v1)
					} else if strings.EqualFold(k, "mul") {
						fm = append(fm, v1)
					}
				} else {
					if strings.EqualFold(k, "pri") {
						mp = append(mp, v1)
					} else if strings.EqualFold(k, "uni") {
						mu = append(mu, v1)
					} else if strings.EqualFold(k, "mul") {
						mm = append(mm, v1)
					}
				}
			}
		}
		if len(fp) > 0 {
			priSingleSelection[k] = fp
		}
		if len(fu) > 0 {
			uniSingleSelection[k] = fu
		}
		if len(fm) > 0 {
			muiSingleSelection[k] = fm
		}
		if len(mp) > 0 {
			priMullSelection[k] = mp
		}
		if len(mu) > 0 {
			uniMullSelection[k] = mu
		}
		if len(mm) > 0 {
			muiMullSelection[k] = mm
		}
	}
	cardinalityGather = append(cardinalityGather, my.mulCardinality(priSingleSelection, i))
	cardinalityGather = append(cardinalityGather, my.mulCardinality(priMullSelection, i))
	cardinalityGather = append(cardinalityGather, my.mulCardinality(uniSingleSelection, i))
	cardinalityGather = append(cardinalityGather, my.mulCardinality(uniMullSelection, i))
	cardinalityGather = append(cardinalityGather, my.mulCardinality(muiSingleSelection, i))
	cardinalityGather = append(cardinalityGather, my.mulCardinality(muiMullSelection, i))
	for _, v := range cardinalityGather {
		if v == nil {
			continue
		}
		curryCardinalityValue := v.(map[string]any)["cardinality"].(uint64)
		curryIndexName := fmt.Sprintf("%v", v.(map[string]any)["indexName"])
		if curryCardinalityValue > maxCardinalityValue {
			cardinalityMaxGather = map[string]uint64{
				curryIndexName: curryCardinalityValue,
			}
			maxCardinalityValue = curryCardinalityValue
		} else if curryCardinalityValue == maxCardinalityValue {
			cardinalityMaxGather[curryIndexName] = curryCardinalityValue
		}
	}
	//处理多个离散度相同的索引名
	multipleMaxCardinalityGather = make(map[string][]int)
	for k := range cardinalityMaxGather {
		if v1, ok := i.IndexColumn[k]; ok {
			multipleMaxCardinalityGather[k] = my.typeSelection(v1)
		}
	}
	//按照数据类型规则找到最优的
	optimalIndexName := my.typeSum(multipleMaxCardinalityGather)
	for _, v := range i.IndexColumn[optimalIndexName] {
		optimalIndexColumnType = append(optimalIndexColumnType, v.ColumnType)
	}
	return global.IndexSelection{
		IndexType:        i.IndexType[optimalIndexName],
		IndexColumn:      i.IndexColumn[optimalIndexName],
		IndexName:        optimalIndexName,
		IndexCardinality: cardinalityMaxGather[optimalIndexName],
		IndexColumnType:  optimalIndexColumnType,
	}
}

// 根据索引列数分为单列索引和多列索引
func (my TableInfoMeta) columnSumGroup(i global.IndexMerge) (q columnSumGroupS) {
	for _, indexName := range i.OrderByIndexName {
		if v, ok := i.IndexColumn[indexName]; ok {
			if len(v) == 1 {
				q.singleColumn = append(q.singleColumn, indexName)
			} else if len(v) > 1 {
				q.multipleColumn = append(q.multipleColumn, indexName)
			}
		}
	}
	return
}

type columnSumGroupS struct {
	singleColumn   []string //索引名
	multipleColumn []string //索引名
}
type indexTypeGroupS struct {
	Primary   []string `json:"primary"`   //主键索引名
	UniqueKey []string `json:"uniqueKey"` //唯一索引名
	Key       []string `json:"key"`       //普通索引名
}
type columnTypeGroupS struct {
	name       string
	class      string //类型
	null       string //null值约束
	dispersion string //离散度
}

// 针对指定的索引名集合，做索引类型做聚合
func (my TableInfoMeta) indexTypeGroup(indexGarth []string, i global.IndexMerge) (q indexTypeGroupS) {
	for _, v := range indexGarth {
		if key, ok := i.IndexType[v]; ok {
			switch key {
			case "pri":
				q.Primary = append(q.Primary, v)
			case "uni":
				q.UniqueKey = append(q.UniqueKey, v)
			case "mul":
				q.Key = append(q.Key, v)
			}
		}
	}
	return
}

// 针对指定的索引名集合，做索引类型做聚合
func (my TableInfoMeta) columnTypeGroup(indexName string, i global.IndexMerge) (q []columnTypeGroupS) {
	if columnGarth, ok := i.IndexColumn[indexName]; ok {
		for _, v := range columnGarth {
			q = append(q, columnTypeGroupS{
				name:       v.ColumnName,
				class:      v.ColumnType,
				null:       v.Null,
				dispersion: v.Cardinality,
			})
		}
	}
	return
}
func (my TableInfoMeta) columnTypePriority(q []columnTypeGroupS) (r []int) {
	if len(q) == 0 {
		return
	}
	for _, v := range q {
		switch strings.Split(strings.ToUpper(v.class), "(")[0] {
		case "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "BIGINT": //整数数值类型
			r = append(r, 1)
		case "FLOAT", "DOUBLE": //单精度和双精度的浮点数类型
			r = append(r, 1)
		case "DECIMAL": //定长数值类型
			r = append(r, 2)
		case "DATE", "TIME", "YEAR", "DATETIME", "TIMESTAMP": //时间类型
			r = append(r, 1)
		case "CHAR": //字符串定长
			r = append(r, 3)
		case "VARCHAR": //字符串变长
			r = append(r, 3)
		default:
			r = append(r, 1000)
		}
	}
	return
}
func (my TableInfoMeta) columnNullGroup(q []columnTypeGroupS) (r []int) {
	for _, v := range q {
		if strings.EqualFold(v.null, "no") {
			r = append(r, 0)
		} else {
			r = append(r, 2)
		}
	}
	return
}
func (my TableInfoMeta) columnDispersion(q []columnTypeGroupS) (r []int64) {
	for _, v := range q {
		if len(v.dispersion) == 0 {
			r = append(r, 0)
			continue
		}
		if t, err := strconv.ParseInt(v.dispersion, 10, 64); err != nil {
			r = append(r, 0)
		} else {
			r = append(r, t)
		}
	}
	return
}
func (my TableInfoMeta) costChoice(i global.IndexMerge) (result global.IndexColumnSumGroup, err error) {
	var (
		indexTypeGarth map[string]any
	)
	type kv struct {
		key   string
		value int64
	}

	a := my.columnSumGroup(i)
	for k1, indexGarth := range [][]string{
		a.singleColumn, a.multipleColumn,
	} {
		q := global.IndexPrioritySort{}
		//比较单、多列索引
		if indexTypeGarth, err = global.StructToMapAny(my.indexTypeGroup(indexGarth, i)); err != nil {
			return
		}
		var pp = make(map[string]any)
		for k, v := range indexTypeGarth {
			var (
				indexNameGarth   []string
				sortedDispersion []kv
				dispersionPairs  []map[string]any
			)
			if v == nil {
				continue
			}
			switch fmt.Sprintf("%v", reflect.TypeOf(v)) {
			case "[]string":
				indexNameGarth = v.([]string)
			}
			if len(indexNameGarth) == 0 {
				continue
			}
			//按照列的离散度进行排序
			for _, indexName := range indexNameGarth {
				o := my.columnDispersion(my.columnTypeGroup(indexName, i))
				total := func() (sum int64) {
					for seq, dispersion := range o {
						if seq == len(o)-1 {
							sum = dispersion
						}
					}
					return
				}()
				sortedDispersion = append(sortedDispersion, kv{
					key:   indexName,
					value: total,
				})
			}
			sort.Slice(sortedDispersion, func(m, j int) bool {
				if sortedDispersion[m].value == sortedDispersion[j].value { //离散度相同
					//先比较数据类型优先级
					srcClassPriority := my.columnTypePriority(my.columnTypeGroup(sortedDispersion[m].key, i))
					srcClassTotal := func() (sum int) {
						for _, priorityValue := range srcClassPriority {
							sum += priorityValue
						}
						return
					}()
					dstClassPriority := my.columnTypePriority(my.columnTypeGroup(sortedDispersion[j].key, i))
					dstClassTotal := func() (sum int) {
						for _, priorityValue := range dstClassPriority {
							sum += priorityValue
						}
						return
					}()
					switch {
					case srcClassTotal > dstClassTotal:
						return sortedDispersion[m].value < sortedDispersion[j].value
					case srcClassTotal < dstClassTotal:
						return sortedDispersion[m].value > sortedDispersion[j].value
					}
					//再比较null值优先级
					srcNullPriority := my.columnNullGroup(my.columnTypeGroup(sortedDispersion[m].key, i))
					srcNullTotal := func() (sum int) {
						for _, priorityValue := range srcNullPriority {
							sum += priorityValue
						}
						return
					}()
					dstNullPriority := my.columnNullGroup(my.columnTypeGroup(sortedDispersion[j].key, i))
					dstNullTotal := func() (sum int) {
						for _, priorityValue := range dstNullPriority {
							sum += priorityValue
						}
						return
					}()
					switch {
					case srcNullTotal > dstNullTotal:
						return sortedDispersion[m].value < sortedDispersion[j].value
					case srcNullTotal < dstNullTotal:
						return sortedDispersion[m].value > sortedDispersion[j].value
					}
				}
				return sortedDispersion[m].value > sortedDispersion[j].value // 按 value 倒序
			})
			for _, subDispersion := range sortedDispersion {
				dispersionPairs = append(dispersionPairs, map[string]any{"name": subDispersion.key, "column": func() (m []string) {
					for _, col := range my.columnTypeGroup(subDispersion.key, i) {
						m = append(m, col.name)
					}
					return
				}(), "cardinality": subDispersion.value})
			}
			pp[k] = dispersionPairs
		}
		var jsonData []byte
		if jsonData, err = json.Marshal(pp); err != nil {
			return
		}
		if err = json.Unmarshal(jsonData, &q); err != nil {
			return
		}
		switch k1 {
		case 0:
			result.Single = q
		case 1:
			result.Multiple = q
		}
	}
	return
}

/*
IndexChoice MySQL 表的索引选择
*/
func (my TableInfoMeta) IndexChoice(s global.TablesMetaInfoInput) (result any, err error) {
	var (
	//Event = "[IndexChoice]"
	)
	if len(s.IndexColumn) == 0 {
		return
	}
	//索引列处理，联合索引进行列合并
	//去除主键索引列、唯一索引列、普通索引列的所有列明
	f1 := my.IndexMerge(s)
	if global.IsEmptyStruct(f1) {
		return
	}
	p := my.keyChoice(f1)
	return p, nil
}
func (my TableInfoMeta) IndexOptimizerTrace(s global.TablesMetaInfoInput) (any, error) {
	var (
		result global.IndexColumnSumGroup
		err    error
	)
	if len(s.IndexColumn) == 0 {
		//return global.IndexSelection{}
	}
	//索引列处理，联合索引进行列合并
	//去除主键索引列、唯一索引列、普通索引列的所有列明
	f1 := my.IndexMerge(s)
	if global.IsEmptyStruct(f1) {
		//return global.IndexSelection{}
	}
	if result, err = my.costChoice(f1); err != nil {
		return nil, err
	}
	return result, nil
}

// IndexStatistics 获取MySQL表的索引统计信息
func (my TableInfoMeta) IndexStatistics(s global.TablesMetaInfoInput) (res any, err error) {
	var (
		indexColumnData []global.IndexStatisticsS
		strSql          string
		event           = "[MySQLIndexStatistics]"
		f1              any
	)
	strSql = fmt.Sprintf("SELECT \n"+
		"table_schema as `databaseName`,\n"+
		"table_name as `tableName`,\n"+
		"NON_UNIQUE as `uniqueType`,\n"+
		"INDEX_NAME as `indexName`,\n"+
		"SEQ_IN_INDEX as `columnSeq`,\n"+
		"COLUMN_NAME as `columnName`,\n"+
		"SUB_PART as `prefix`,\n"+
		"CARDINALITY as `cardinality` \n"+
		"FROM \n"+
		"information_schema.STATISTICS \n"+
		"where \n"+
		"table_schema in ('%v') and table_name in ('%v')\n"+
		"order by INDEX_NAME,SEQ_IN_INDEX", s.TableInfo.Schema, s.TableInfo.Table)
	if _, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: strSql,
		Expect:       ETL.SMap,
	}); err != nil {
		err = errors.New(fmt.Sprintf("%v %v", event, err))
		return
	}
	for _, v := range f1.([]map[string]interface{}) {
		var dd = global.IndexStatisticsS{}
		var jsonData []byte
		for g, j := range v {
			v[g] = fmt.Sprintf("%v", j)
		}
		jsonData, err = json.Marshal(v)
		if err != nil {
			err = errors.New(fmt.Sprintf("%v []map[string]any strconv json fail. Execution process{strconv data:%v error:%v}", event, v, err))
			return nil, err
		}
		// 反序列化JSON字符串到结构体
		err = json.Unmarshal(jsonData, &dd)
		if err != nil {
			err = errors.New(fmt.Sprintf("%v []byte json unmarshal struct fail. Execution process{strconv data:%v error:%v}", event, string(jsonData), err))
			return nil, err
		}
		indexColumnData = append(indexColumnData, dd)
	}

	return indexColumnData, err
}

/*
IndexColumn 查询MySQL库下指定表的索引统计信息
*/
func (my TableInfoMeta) IndexColumn(s global.TablesMetaInfoInput) (any, error) {
	var (
		indexColumnData                []global.IndexColumn
		event                          = "[mysqlIndexColumn]"
		columnResult, statisticsResult any
		err                            error
		p                              TableColumn
	)
	if columnResult, err = p.All(global.GetColumnTypeInput{DB: s.DB, TableInfo: s.TableInfo, Input: s.Input}); err != nil || columnResult == nil {
		err = errors.New(fmt.Sprintf("%v get Failed to obtain index information. error is:%v", event, err))
		return nil, err
	}
	if statisticsResult, err = my.IndexStatistics(s); err != nil {
		err = errors.New(fmt.Sprintf("%v get Failed to obtain index information. error is:%v", event, err))
		return nil, err
	}
	if statisticsResult == nil {
		return nil, err
	}
	for _, v := range statisticsResult.([]global.IndexStatisticsS) {
		var dd = global.IndexColumn{
			Schema:        v.Schema,
			Table:         v.Table,
			IndexName:     v.IndexName,
			ColumnName:    v.IndexColumn,
			IndexSeq:      v.IndexSeq,
			SubPart:       v.PreFix,
			Cardinality:   v.IndexCardinality,
			IndexCategory: "local",
		}
		dd.ColumnKey = func() string {
			switch {
			case strings.TrimSpace(v.UniqueType) == "0":
				switch strings.TrimSpace(v.IndexName) {
				case "PRIMARY":
					return "P"
				default:
					return "U"
				}
			default:
				return "M"
			}
		}()
		if dd.ColumnKey == "P" {
			dd.IndexName = "PRIMARY"
		}
		for _, k := range columnResult.(global.AllReturnResult).Result {
			if v.IndexColumn == k.ColumnName {
				dd.ColumnType = k.ColumnType
				dd.ColumnSeq = fmt.Sprintf("%v", k.ColumnSeq)
				break
			}
		}
		indexColumnData = append(indexColumnData, dd)
	}
	return indexColumnData, err
}
